Urthman's MDB Code Generator Design Notes

Common Answers:

1. Development platform: The program has been tested both extensively and successfully on Windows 95, Windows 98 and WIndows NT v4.0, with both MS Office 95 and 97. The development platform of choice is Visual Basic v5.0 Professional Edition with Service Pack 3.

2. MS Jet 3.5x compatibility issue: Earliest releases of the MDB Code Generator were built with reference to the Microsoft DAO 3.51 Library. These early releases were also scaled down packages for size with the mistaken assumption that everyone using this program has both Microsoft Visual Basic v5.0 with Service Pack 3 and a version of Microsoft Access 95 or 97 with all of the available updates.

This current release is distributed with a more complete package including all of the latest MS Jet components necessary and available at the time of release.

Most recently, an Internet security issue has arisen dealing with the ODBC component included with this software package. Although this software doesn't use ODBC, the component has been updated to the preferred version for MS Jet v4.0.

3. Visual Basic Versions: Visual Basic v6.0 users will most likely require the Visual Basic v5.0 runtimes. No one has reported trying out the code modules generated by our software with VB4 projects.

4. Installation Problems: This program's installer is a slightly modified version of the native installer included with the Visual Basic 5.0 Professional Edition. Refer to the "Code Chunks" section of our web site for references to this "slight modification". Much greater control over the programs installation was established using the Urthman's Setup LST Editor also available at our web site.

Design Notes:

1. Dynamic Database Creation: The code generator table-create function has been moved into the routine that opens the table within the database. This allows for upgradable code utilizing the original (existing) database:

When a database is accessed for the first time, it is the result of a call to access a table within that database. If the database file does not exist, then the database file is created, and since the table being accessed also doesn't exist, then that table is also created with all of it's fields and indexes from the original snapshot database from which the code module was generated.

Under this strategy, any table that is never accessed would never be created, and any subsequent upgrades that use additional tables can then be dropped onto the original database and the additional tables will be created within that database without the need to upgrade or otherwise convert the original data.

2. Maintaining synchronization with multiple keys: When non-primary keys exist (in addition to the primary), the save and record delete routines will restore the primary key to do the necessary validation-search prior to the actual function of the respective routines. Immediately following the save or delete, the original key which was active prior to the function of the subroutine is again restored, and the file re-synchronized with that key/index based on the key value of the same or next higher record. If this same-or-next-higher record is not a valid record, the same-or-next-lower record is found.

3. Find function extensions: The "Find Extensions" can be used as an alternative to the "Next" and "Previous" function and as a recovery from a failed "Find" operation. Where the standard "Find" or "By" routines search for a record that matches the search criteria ("="), the FindNX and FindPV search for the next higher or next lower record, which allows for a much looser criteria.

For example, given a "Find" (or "By") operation to seek a specific criteria; if the search fails, you might want to look for the record immediately above or below that record rather than leave the database without a current record. A standard Next or Previous may not provide the record immediately above or below the failed search.

4. File Type Registration: In the advanced options of the registered version, there is an option to include a registry entry which adds a file association and corresponding Windows Explorer file menu function. This means that you can use Windows Explorer to locate your MDB files, right-click on them and select VB Code Generator from the pop up menu. The code generator is started with that database loaded and ready to generate (or re-generate) code.

5. Improved Project Management: In the original release, registered versions of the program would build and maintain a project listing allowing the user to select a previously generated database definition and pop the code out with all of the selected tables and database specific options already in place. This is great for when you need to redesign the database and table layouts.

The new release manages this data in separate configuration files which can then be copied for backup or deleted individually.

6. Table Field Verification: As a natural extension of table creation "on-the-fly -- if-and-when needed", a table field verification subroutine has been added to the extensive code generated by this program. In the event that your program must be upgraded to include additional fields in a given table, rather than having to require your end user dump or convert their original database, this feature can jump into a field verification routine that will create the additional fields in the database table.

However: Specific changes in the index structure and modifications for field sizes are not available at this time. Also, deleted fields will merely be ignored. If a discontinued field is either "Required" or does not "Allow Zero Length", saving new records will result in an error.

7. Read-Write Permissions: Another natural extension of an existing feature has been added to the code generator arsenal. In the advanced options, there is a selection for how you want your program to address security: either by the default "admin" with no password for all users; or with a user name and (optional) password that your program will pass along into the code module.

When the user name and (optional) password is controlled by your program, the save and kill subroutines are changed to a "Function As Boolean" routine, returning a True or False to indicate whether the operation was successful or not. If the designated user does not have write permissions, the save and kill operations will return a False, and if the operation was successful, the operation returns a True.

8. Find Extensions: These find extensions no longer have an option for passing parameters. Instead, the Find and By routines will leave the index field variables populated with the initial parameters if the find operation fails. Thus, you don't need to worry about the parameters or field data after a failed Find:

call mdbMyData.MyTableFind(Data1, Data2, vbNullString)
if mdbMyDate.MyTableERR Then call mdbMyData.MyTableFindNX

9. IsKey function: This function returns a True if the current index is the Primary Key, otherwise returns a False.